#!/usr/bin/perl
#
# Write the "short" report that lists Linux users
# by place of work, country and how long they have used Linux
#
# The country table also includes registered friends and machines
#

require "reportsub.pl";
use Counter;

#===================== DATA FETCH =======================

$places = Counter::openfile("places");
@countries = $places->select("type = 'country'");
$persons = Counter::openfile("persons");
%personcounts = $persons->counts("country");
$machines = Counter::openfile("machines");
%machinecounts = $machines->counts("country");
$friends = Counter::openfile("friends");
%friendcounts = $friends->counts("country");


# Calculate some other numbers
for $crec (@countries) {
    # Check the usercount to see if recount is needed
    if ($$crec{users} != $personcounts{"\L$$crec{name}"}) {
	my $ccount = $personcounts{"\L$$crec{name}"};
	warn "Recount $$crec{name}? count $ccount stored $$crec{users}\n";
    }
    if ($$crec{population} > 0) {
	$$crec{userdensity} = $$crec{users} / $$crec{population};
    } elsif ($$crec{users} > 0) {
	print STDERR "$$crec{name}: Users but no population\n";
    }
    $$crec{machines} = $machinecounts{"\L$$crec{name}"};
    $$crec{friends} = $friendcounts{"\L$$crec{name}"};
}

# Side effect (arg): store numbers into a table
$FileTable::dbhandle->do("DROP TABLE temp_short"); # ignore error
$FileTable::dbhandle->do("
CREATE TABLE temp_short (
  country char(2) PRIMARY KEY,
  name varchar(64),
  hostcount int DEFAULT 0,
  population float DEFAULT 0,
  userdensity float DEFAULT -1,
  users int DEFAULT 0,
  machines int DEFAULT 0,
  friends int DEFAULT 0,
  rank int
)") || die "CREATE TABLE temp_short failed\n";

$rank = 1;
$insertstm = $FileTable::dbhandle->prepare("INSERT temp_short VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
    || die "Error in prepare INSERT";
for $crec (sort {$b->{userdensity} <=> $a->{userdensity}} @countries) {
    $country = $crec->{name};
    if (length($country) > 2) {
	warn "Error in data: Country name > 2 chars: $country\n";
	next;
    }
#    warn "Inserting for $$crec{name}\n";
    $insertstm->execute($$crec{name},
			 $$crec{longname},
			 $$crec{hostcount},
			 $$crec{population},
			 $$crec{userdensity},
			 $$crec{users},
			 $$crec{machines},
			 $$crec{friends},
                         $rank
			 ) || die "Error in INSERT";
    ++ $rank;
}
    



$personcount = $persons->count();
$friendcount = $friends->count();
$machinecount = $machines->count();
$date = localtime;

# =============== Introductory remarks ==========================
$mincount = printwithcommas($personcount*20);
$maxcount = printwithcommas($personcount*500);
print <<EoF;
<?php include "counter.php" ?>
<HTML>
<?php pagehead("Summary Report") ?>

<body>
<?php pagetop("Linux Counter Summary Report") ?>
This is the Linux Counter summary as of $date
<p>
There are $personcount persons registered.<br>
There are $machinecount machines registered.<br>
<p>
I guesstimate that between 0.2% and 5% of all Linux users have
registered with the Linux Counter.
So the total number of Linux users is probably between
$mincount and $maxcount people.
<h2>The history of the Linux user count</h2>
<img src="all-users.png">
<br>
The top curve shows the number of registered Linux users.<br>
The bottom curve shows the number of "active" Linux users - those who have 
<a href="/login.php">visited</a> their account
on the counter in the last 2 years.<br>
In November 2001, "inactive" users were <a href="/news/deletion.php">removed</a>
from the count; since January, the
inactive users are removed continuously.
EoF


# =============== List countries by density =====================

# The following prints a PHP statement - the number of indirections can be a BIT perplexing...
print << 'EoF';
<?php
    $orderby = $counter->get_input("orderby", "", "get");
    $limit = $counter->get_input("limit", "", "get");
    if (!$orderby) { $orderby = "userdensity"; }
    if (!$limit) { $limit = 1; }
?>
<a name="table"></a>
<h2>WHERE LINUX USERS LIVE</h2>
The table is sorted by
<?php
    if ($orderby == "userdensity") {
	print "number of users divided by population\n";
    } else {
	print $orderby;
    }
   if ($limit > 1) {
       print "Countries with less than $limit users are not listed\n";
   }
?>
<table border=4>
<tr><th>No<th>&nbsp;<th align=left>Country
<!-- note - RFC 2396 specifies that fragment follows query part -->
<th><a href="/reports/short.php?orderby=users#table">Pers</a>
<th><a href="/reports/short.php?orderby=machines#table">Mach</a>
<th><a href="/reports/short.php?orderby=userdensity#table">P/Mpop</a>
<th><a href="/reports/short.php?orderby=population#table">People</a>
<?php
    
    $query = mysql_query("select * from temp_short where users >= $limit order by $orderby desc");

    if ($query == 0) {
       print("</table>MySQL error: " . mysql_errno() . ":" . mysql_error());
       die("Query for count failed");
    }
    $rank = 1;
    while ($arr = mysql_fetch_array($query)) {
        print "<tr align=right><td>$rank";
        print "<td align=left>$arr[country]";
        print "<td align=left><a href=\"/reports/place.php?place=$arr[country]\">$arr[name]</a>";
        printf("<td>%d", $arr[users]);
        printf("<td>%d", $arr[machines]);
        printf("<td>%5.2f", $arr[userdensity]);
        if ($arr[population] >= 1) {
	    printf("<td>%6.1fM", $arr[population]);
	} else {
	    printf("<td>%6.0fK", $arr[population] * 1000);
	}
        ++ $rank;
    }
?>
EoF
print "</table>\n\n";

#==================== Table of users and places ==================
%usages = $persons->counts("usage");
for $id (keys(%usages)) {
    if ($id =~ /, /) {
	@newid = split(/, /, $id);
	for $id2 (@newid) {
	    $usages{$id2} += $usages{$id};
	}
	delete $usages{$id};
    }
}

print "<h2>WHERE PEOPLE USE LINUX</h2>\n";
printf "<table border=4 cellpadding=2>\n";
printf "<tr align=right><th align=left>%-10s</th><th>%5s</th><th>%5s</th></tr>\n",
    "Place", "Users", "Percent";
for $usage (keys(%usages)) {
    printf "<tr align=right><td align=left>%-10s</td><td>%5d</td><td>%6.2f%%</td></tr>\n",
    $usage?$usage:"not specified", $usages{$usage},
    100 * $usages{$usage} / $personcount;
    $answercount += $usages{$usage};
}
print "</table>\n";

print <<EoF;
<p>
The total number of answers is $answercount for $personcount registered users.

<h2>WHEN PEOPLE STARTED USING LINUX</h2>
<img src="started.png">
<br>
This graph shows when people say they started to use Linux, and also the rate of registration with the Linux Counter.
<p>
Note: This is based on the $perssums{started}{total} people who answered
this question, less the answers that were not computable or wildly wrong
(such as starttimes before 1991 or after the current year).
<p>
The peaks in December occur because unknown month names are moved
to December, in order to avoid giving an impression of too early
adoption of Linux.
<p>
The two first large peaks in the registration rate probably correspond to
the opening of the counter and the introduction of Web-based registration.
<p>
Among the implications one can read out of this graph is that
it may take a while from the time someone starts using Linux until
he registers with the counter.
<?php pagebottom() ?>
</body>
</html>
EoF
